from DAL.sqlHelper import (get_keyboard_conn as conn_a,SQL_ADD_FIELD_VALUES
,SQL_UPDATE_SET,SQL_ORDER,SQL_WHERE)
"""
权限管理模块，包括登录用户管理、角色管理、模块管理
"""
class usersDAL:
    def getList(self, account="", state="", is_admin=""
                ,page=1,limit=20,filed='id',sort='ascending'):
        sql = """
            SELECT id,account,token,state,is_admin,DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS create_time
            FROM `login_users`         
            {0}
            {1}
            
           """
        sql_where = " WHERE account like '%{0}%' ".format(account)
        if state:
            sql_where += " AND  state = '{0}' ".format(state)
        if is_admin:
            sql_where += " AND is_admin = '{0}' ".format(is_admin)
        sql_order = SQL_ORDER(page,limit,filed,sort)
        sql = sql.format(sql_where,sql_order)
        # print(sql)
        sql_count ="""
            SELECT count(id) as count
            FROM `login_users`         
            {0}            
           """
        sql_count = sql_count.format(sql_where)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                cursor.execute(sql_count)
                conn.commit()
                res1 = cursor.fetchone()
                return res,res1["count"]
        finally:
            conn.close()

    def getListById(self, id=0):
        sql = """
                SELECT id,account,state,is_admin,create_time
                FROM `login_users`
                WHERE id='{0}'
                 """
        sql = sql.format(id)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchone()
                return res
        finally:
            conn.close()

    def resetPWD(self,id=0,old_password="",password=""):

        sql = """
        UPDATE  login_users
        SET `password`='{2}'
        WHERE id='{0}' and `password`='{1}'
            """
        sql = sql.format(id,old_password, password)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def initPWD(self,id=0,password=""):

        sql = """
        UPDATE  login_users
        SET `password`='{1}'
        WHERE id='{0}'
            """
        sql = sql.format(id, password)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def existAccount(self,account=""):

        sql = """
        SELECT id 
        FROM `login_users`
        WHERE account='{0}'
            """
        sql = sql.format(account)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchone()
                return (res and 1) or 0
        finally:
            conn.close()
    """
    这个是真删除，有可能让系统信息紊乱
    """
    def deleteById(self, id=0):
        sql = """
        DELETE 
        FROM  login_users
        WHERE id='{0}'
           """
        sql = sql.format(id)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

        """
        这个是假删除，但是仍然保存历史信息，让系统数据完整
         """
    def removeById(self, id=0):
        sql = """
            UPDATE  login_users
            SET `state`=5
            WHERE id='{0}'
              """
        sql = sql.format(id)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def recovery(self, id=0):
        sql = """
            UPDATE  login_users
            SET `state`=0
            WHERE id='{0}'
              """
        sql = sql.format(id)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def add(self, account=None, password='1234'):
        if not account:
            return 0
        sql = """
            INSERT INTO
            login_users(`account`,`password`,`state`,`is_admin`,`create_time`)
            VALUES('{0}','{1}','0','3',NOW())
                  """
        sql = sql.format(account, password)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()
    """
     state表示 0：未登录，1：已登录，2：冻结，3：注销了，4：不可用,5,已删除
    """
    def updateState(self, id=0, state=0):

        sql = """
                UPDATE  login_users
                SET `state`={1}
                WHERE id={0}
                 """
        sql = sql.format(id, state)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def bindRoles(self, user_id=0,role_ids=[]):
        sql_1 = """
            DELETE 
            FROM login_users_role_ref
            WHERE user_id={0}
                 """
        sql_1 = sql_1.format(user_id)

        sql_values_list = []
        for it in role_ids:
            sql_values_list.append("({0},{1})".format(user_id, it))
        sql_values = ",".join(sql_values_list)

        sql_2 = """
           INSERT INTO
           login_users_role_ref(user_id,role_id)
           VALUES {0}
                 """
        sql_2 = sql_2.format(sql_values)
        # print(sql_2)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                if sql_values_list:
                    cursor.execute(sql_2)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def bindModule(self, user_id=0,module_ids=[]):
        sql_1 = """
                   DELETE 
                   FROM login_users_module_ref
                   WHERE user_id={0}
                        """
        sql_1 = sql_1.format(user_id)

        sql_values_list = []
        for it in module_ids:
            sql_values_list.append("({0},{1})".format(user_id, it))
        sql_values = ",".join(sql_values_list)

        sql_2 = """
                  INSERT INTO
                  login_users_module_ref(user_id,module_id)
                  VALUES {0}
                        """
        sql_2 = sql_2.format(sql_values)
        # print(sql_2)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                if sql_values_list:
                    cursor.execute(sql_2)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def getModuleIdByUser(self, user_id=""):
        sql = """
                SELECT module_id
                FROM `login_users_module_ref`
                WHERE  user_id='{0}'
              """
        sql = sql.format(user_id)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                return res
        finally:
            conn.close()

    def getRoleIdByUser(self, user_id=""):
        sql = """
                SELECT role_id 
                FROM `login_users_role_ref`
                WHERE user_id='{0}'
               """
        sql = sql.format(user_id)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                return res
        finally:
            conn.close()



class moduleDAL:
    def getList(self):
        sql = """
           SELECT id,pguid,guid,module_name,url,state,sortby,DATE_FORMAT(create_time, '%Y-%m-%d %H:%i') AS create_time
           FROM login_module
           ORDER BY sortby ASC
           """
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                return res
        finally:
            conn.close()

    def deleteById(self, id=0):
        sql_1 = """
                DELETE 
                FROM  login_module
                WHERE id='{0}'
           """
        sql_1 = sql_1.format(id)
        sql_2 = """
                DELETE 
                FROM  `login_role_module_ref`
                WHERE module_id='{0}'
                  """
        sql_2 = sql_2.format(id)
        sql_3 = """
                DELETE 
                FROM  `login_users_module_ref`
                WHERE  module_id='{0}'
                  """
        sql_3 = sql_3.format(id)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_2)
                cursor.execute(sql_3)
                cursor.execute(sql_1)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()


    def update(self, id=None,pguid=None,module_name=None, url=None, state=None):
        pram = locals()
        sql_set = SQL_UPDATE_SET(pram, ['id'])
        sql = """
                 UPDATE 
                   `login_module`
                   SET  {1}
                   WHERE id = '{0}'
                 """
        sql = sql.format(id, sql_set)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def add(self, pguid='1',guid=None,module_name=None, url=None, state=None):
        param = locals()
        sql_field,sql_values=SQL_ADD_FIELD_VALUES(param)
        sql = """
               INSERT INTO  `login_module`({0},create_time)
               VALUES({1},NOW())
               """
        sql = sql.format(sql_field,sql_values)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

class rolesDAL:
    def getList(self, role_name="",page=1, limit=20, filed='id', sort='ascending'):
        sql = """
               SELECT id,role_name,DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS create_time
               FROM `login_role`         
               {0}
               {1}
              """
        sql_where = " WHERE role_name like '%{0}%' ".format(role_name)
        sql_order = SQL_ORDER(page, limit, filed, sort)
        sql = sql.format(sql_where, sql_order)
        # print(sql)
        sql_count = """
               SELECT count(id) as count
               FROM `login_role`         
               {0}            
              """
        sql_count = sql_count.format(sql_where)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                cursor.execute(sql_count)
                conn.commit()
                res1 = cursor.fetchone()
                return res, res1["count"]
        finally:
            conn.close()

    def getListAll(self):
        sql = """
               SELECT id,role_name 
               FROM `login_role`
              """
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                return res
        finally:
            conn.close()

    def deleteById(self, id=0):
        sql_1 = """
                DELETE 
                FROM  login_role
                WHERE id='{0}'
           """
        sql_1 = sql_1.format(id)
        sql_2 = """
                DELETE 
                FROM  `login_role_module_ref`
                WHERE role_id='{0}'
                  """
        sql_2 = sql_2.format(id)
        sql_3 = """
                DELETE 
                FROM  `login_users_role_ref`
                WHERE role_id='{0}'
                  """
        sql_3 = sql_3.format(id)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_2)
                cursor.execute(sql_3)
                cursor.execute(sql_1)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def update(self, id=None,role_name=None):
        pram = locals()
        sql_set = SQL_UPDATE_SET(pram, ['id'])
        sql = """
                 UPDATE 
                   `login_role`
                   SET  {1}
                   WHERE id = '{0}'
                 """
        sql = sql.format(id, sql_set)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def add(self, role_name=None):
        param = locals()
        sql_field,sql_values=SQL_ADD_FIELD_VALUES(param)
        sql = """
               INSERT INTO  `login_role`({0},create_time)
               VALUES({1},NOW())
               """
        sql = sql.format(sql_field,sql_values)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def notExistAdd(self, role_name=None):
        param = locals()
        sql = """
               INSERT INTO  `login_role`({0},create_time)
               VALUES({1},NOW())
               """
        sql_field, sql_values = SQL_ADD_FIELD_VALUES(param)
        sql = sql.format(sql_field,sql_values)

        sql_1 = """
        SELECT count(id) AS count
        FROM `login_role`
        WHERE {0}      
        """
        sql_where = SQL_WHERE(param)
        sql_1 = sql_1.format(sql_where)
        # print(sql_1)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res_one = cursor.fetchone()
                # print(res_one)
                if res_one['count']>0:
                    res=0
                    return res
                else:
                    cursor.execute(sql)
                    conn.commit()
                    res = cursor.rowcount
                    return res
        finally:
            conn.close()

    def getModuleIdByRole(self, role_id=""):
        sql = """
                SELECT module_id
                FROM `login_role_module_ref`
                WHERE role_id={0}
              """
        sql = sql.format(role_id)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                return res
        finally:
            conn.close()

    def updateRoletModule(self, role_id="",module_ids=[]):
        sql_delete = """
              DELETE 
                FROM `login_role_module_ref`
                WHERE role_id='{0}'
              """
        sql_delete = sql_delete.format(role_id)
        # print(sql_delete)
        sql_insert = """
                      INSERT into `login_role_module_ref`(role_id,module_id)
                      VALUES {0}
                      """
        sql_values=[]
        for it in module_ids:
            sql_values.append("('{0}','{1}')".format(role_id,it))

        sql_values = ','.join(sql_values)
        sql_insert = sql_insert.format(sql_values)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_delete)
                conn.commit()
                if sql_values:
                    cursor.execute(sql_insert)
                    conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()



if __name__=="__main__":
    dal = rolesDAL()
    # dal.notExistAdd("333")
    re = dal.getModuleIdByRole(1)



